﻿
using IpToBlack.Models;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace IpToBlack.PUB
{
    public class DB
    {
        public DB()
        {
            iSugar.DbMaintenance.CreateDatabase();
            iSugar.CodeFirst.InitTables(typeof(sys_t_system));
            iSugar.CodeFirst.InitTables(typeof(sys_t_ip));
        }
        public SqlSugarClient iSugar
        {
            get
            {
                SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
                {
                    DbType = SqlSugar.DbType.Sqlite,
                    ConnectionString = "DataSource=" + Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "SaasIP.db"),
                    IsAutoCloseConnection = true
                });

                return db;
            }
        }

        /// <summary>
        /// 添加实体数据(为null字段不更新,注意model有默认值的情况)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="db">(事务的db对象)</param>
        /// <returns></returns>
        public int InsertModel<T>(T t) where T : class, new()
        {
            //Where(true/*不插入null值的列*/,false/*不插入主键值*/)
            return iSugar.Insertable(t).
                IgnoreColumns(true, false).ExecuteCommand();
        }

        /// <summary>
        /// 根据主键更新实体，返回影响条数(实体字段要有主键特性)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="db">(事务的db对象)</param>
        /// <returns></returns>
        public int UpdateModel<T>(T t) where T : class, new()
        {
            //字段null，不进行更新
            return iSugar.Updateable(t).IgnoreColumns(ignoreAllNullColumns: true).ExecuteCommand();
        }

        /// <summary>
        /// 根据条件表达式更新实体(指定要更新的列)，返回影响条数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="columns"></param>
        /// <param name="expressionWhere">条件表达式</param>
        /// <param name="db">(事务的db对象)</param>
        /// <returns></returns>
        public int UpdateModelCol<T>(T t, Expression<Func<T, object>> columns, Expression<Func<T, bool>> expressionWhere) where T : class, new()
        {
            return iSugar.Updateable(t).UpdateColumns(columns).Where(expressionWhere).ExecuteCommand();
        }
        /// <summary>
        /// 动态更新
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="columns">要更新的字段</param>
        /// <param name="expressionWhere">where条件表达式</param>
        /// <param name="db"></param>
        /// <returns></returns>
        public int Update<T>(Expression<Func<T, T>> columns, Expression<Func<T, bool>> expressionWhere) where T : class, new()
        {
            return iSugar.Updateable<T>().SetColumns(columns).
                //IgnoreColumns(ignoreAllNullColumns: true).//不能加此方法，会有“CommandText 属性尚未初始化”异常
                Where(expressionWhere).ExecuteCommand();
            /* 调用示例
             Update<Entity.SysAdmin>(p => new Entity.SysAdmin { photo = photo, Password = newPwd }
                ,p => p.ID == sm.id && p.Password == oldPwd ,db) > 0;
             */
        }
        /// <summary>
        /// 功能描述:查询所有数据
        /// </summary>
        /// <returns>数据列表</returns>
        public List<T> Query<T>(Expression<Func<T, bool>> whereExpression) where T : class, new()
        {
            return iSugar.Queryable<T>().WhereIF(whereExpression != null, whereExpression).ToList();
        }
        public List<T> Query<T>(string sql, object param = null)
        {
            return iSugar.Ado.SqlQuery<T>(sql, param).ToList();
        }
        public T QueryForFist<T>(string sql, object param = null)
        {
            return iSugar.Ado.SqlQuery<T>(sql, param).FirstOrDefault();
        }
        /// <summary>
        /// 增删改
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns>Number of rows affected</returns>
        public int Execute(string sql, object param = null)
        {
            return iSugar.Ado.ExecuteCommand(sql, param);
        }
    }
}
